﻿CREATE PROCEDURE [dbo].[User_Create]
	@email nvarchar(256)
	,@passwordHash nvarchar(128)
	,@passwordSalt nvarchar(128)
AS
BEGIN

	DECLARE @userHashCode nvarchar(32)

	SET @userHashCode = CAST([dbo].[RandString](32) AS NVARCHAR(32))
	WHILE EXISTS (SELECT * FROM [dbo].[user_User] WHERE [HashCode] = @userHashCode)
	BEGIN
		SET @userHashCode = CAST([dbo].[RandString](32) AS NVARCHAR(32))
	END

	SET @email = LOWER(@email)
	IF EXISTS (SELECT * FROM [dbo].[user_User] WHERE Email = @email)
	BEGIN
		IF NOT EXISTS (SELECT * FROM [dbo].[user_User] WHERE Email = @email AND PasswordHash = @passwordHash AND PasswordSalt = @passwordSalt)
		BEGIN
			RAISERROR ('EmailBusy',9,1)
			RETURN
		END
		ELSE
		BEGIN
			UPDATE [dbo].[user_User]
				 SET [HashCode] = @userHashCode
						,[ActivityDate] = GETDATE()
			 WHERE Email = @email AND PasswordHash = @passwordHash AND PasswordSalt = @passwordSalt

			 SELECT @userHashCode AS 'HashCode'

			 RETURN
		END
	END

	BEGIN TRAN

	DECLARE @emailSha1 VARCHAR(32)
	SET @emailSha1 = CONVERT(VARCHAR(32), HashBytes('SHA1', @email), 2)

	WHILE EXISTS (SELECT * FROM [dbo].[user_User] WHERE [EmailSh1] = @emailSha1)
	BEGIN
		SET @emailSha1 = CONVERT(VARCHAR(32), HashBytes('SHA1', @email + CAST([dbo].[RandString](32) AS NVARCHAR(32))), 2)
	END

	INSERT INTO [dbo].[user_User]
						 ([Email]
						 ,[PasswordHash]
						 ,[PasswordSalt]
						 ,[HashCode]
						 ,[ActivityDate]
						 ,[EmailSh1]
						 )
	VALUES
				(@email
				,@passwordHash
				,@passwordSalt
				,@userHashCode
				,GETDATE()
				,@emailSha1
				)

	COMMIT TRAN

	SELECT @userHashCode AS 'HashCode'


END